SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO











CREATE PROC [dbo].[SP_TASALD_EMPR_Q23]
/*------------------------------------------------------------*/
/*----- Empresa          : OFISIS S.A.                    ----*/
/*----- Cliente          : OFISIS S.A.                    ----*/
/*----- Sistema          : Contabilidad                   ----*/
/*----- Modulo           : Reporteador Financiero         ----*/
/*----- Programa         : Consulta de Saldos             ----*/
/*----- Script           : TASALD_EMPR                    ----*/
/*----- Nombre           : SP_TASALD_EMPR_Q23             ----*/
/*----- Desarrollado por : Martin Garcia Farfan           ----*/
/*----- Fecha            : 03/05/1999                     ----*/
/*----- Base de Datos    : MS SQL Server                  ----*/
/*----- Version          : 6.5                            ----*/
/*----- Invoca a SP      :                                ----*/
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
/*----- Modificado 1 por : Martin Garcia Farfan           ----*/
/*----- Fecha Modifica   : 08/05/1999                     ----*/
/*----- Detalle Modifica : Se cambio selects por MACROS   ----*/
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
/*----- Modificado 2 por : Martin Garcia Farfan           ----*/
/*----- Fecha Modifica   : 27/01/2000                     ----*/
/*----- Detalle Modifica : Longitud del Auxiliar   ----*/
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
/*----- Comentarios      : Consulta de Saldos modalidad   ----*/
/*-----                    de Empresa                     ----*/
/*----- @ISTI_CONS_SALD = 'SAL' - Saldos de Empresa       ----*/
/*----- @ISTI_CONS_SALD = 'FLE' - Saldos de Flujo         ----*/
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
/*----- Drop proc SP_TASALD_EMPR_Q23                      ----*/
/*----- Grant all on SP_TASALD_EMPR_Q23 to public         ----*/
/*----- SP_TASALD_EMPR_Q23 '01', 1, '001', '@SAL', '42', '', 
        '', ''                                            ----*/
/*------------------------------------------------------------*/
@ISCO_EMPR          TD_VC_002,
@INNU_CNTB_EMPR     TD_IN_001,
@ISCO_REPO          TD_VC_003,
@ISCO_FORM          TD_VC_008,
@ISCO_CNTA_EMPR     TD_VC_010,
@ISTI_AUXI_EMPR     TD_VC_001,
@ISCO_AUXI_EMPR     TD_VC_020,
@ISCO_ORDE_SERV     TD_VC_020,
@ISTI_CONS_SALD     TD_VC_003
As

Declare
 @VSNU_COLU_0001    TD_VC_010,
 @VSNU_COLU_0002    TD_VC_010,
 @VSNU_COLU_0003    TD_VC_010,
 @VSNU_COLU_0004    TD_VC_010,
 @VSNU_COLU_0005    TD_VC_010,
 @VSNU_COLU_0006    TD_VC_010,
 @VSNU_COLU_0007    TD_VC_010,
 @VSNU_COLU_0008    TD_VC_010,
 @VSNU_COLU_0009    TD_VC_010,
 @VSNU_COLU_0010    TD_VC_010,
 @VSNU_COLU_0011    TD_VC_010,
 @VSNU_COLU_0012    TD_VC_010,
 @VSNU_COLU_0013    TD_VC_010,
 @VSNU_COLU_0014    TD_VC_010,
 @VSNU_COLU_0015    TD_VC_010,
 @VSNU_COLU         TD_VC_010,
 @VNNU_CONT         TD_IN_001,
 @VNIM_SALD         TD_NU_016_004,
 @VSCO_CNTA_TEMP    TD_VC_010,
 @VSTI_AUXI_TEMP    TD_VC_001,
 @VSCO_AUXI_TEMP    TD_VC_020,
 @VSCO_ORDE_TEMP    TD_VC_020,
 @CNIM_SALD_TEMP    TD_NU_016_004,
 @VSDE_SELE_0001    varchar(255),
 @VSDE_SELE_0002    varchar(255),
 @VSDE_SELE_0003    varchar(255),
 @VSDE_SELE_0004    varchar(255)

create table #TWSALD_EMPR_Q23_1
(IM_MVTO_0001  varchar(20)  NULL,
 IM_MVTO_0002  varchar(20)  NULL,
 IM_MVTO_0003  varchar(20)  NULL,
 IM_MVTO_0004  varchar(20)  NULL,
 IM_MVTO_0005  varchar(20)  NULL,
 IM_MVTO_0006  varchar(20)  NULL,
 IM_MVTO_0007  varchar(20)  NULL,
 IM_MVTO_0008  varchar(20)  NULL,
 IM_MVTO_0009  varchar(20)  NULL,
 IM_MVTO_0010  varchar(20)  NULL,
 IM_MVTO_0011  varchar(20)  NULL,
 IM_MVTO_0012  varchar(20)  NULL,
 IM_MVTO_0013  varchar(20)  NULL,
 IM_MVTO_0014  varchar(20)  NULL,
 IM_MVTO_0015  varchar(20)  NULL)

 
Select @VSNU_COLU_0001 = rtrim(ltrim(t1.IM_COLU_0001 + t2.IM_COLU_0001 + t3.IM_COLU_0001)),
       @VSNU_COLU_0002 = rtrim(ltrim(t1.IM_COLU_0002 + t2.IM_COLU_0002 + t3.IM_COLU_0002)), 
       @VSNU_COLU_0003 = rtrim(ltrim(t1.IM_COLU_0003 + t2.IM_COLU_0003 + t3.IM_COLU_0003)),
       @VSNU_COLU_0004 = rtrim(ltrim(t1.IM_COLU_0004 + t2.IM_COLU_0004 + t3.IM_COLU_0004)), 
       @VSNU_COLU_0005 = rtrim(ltrim(t1.IM_COLU_0005 + t2.IM_COLU_0005 + t3.IM_COLU_0005)),
       @VSNU_COLU_0006 = rtrim(ltrim(t1.IM_COLU_0006 + t2.IM_COLU_0006 + t3.IM_COLU_0006)), 
       @VSNU_COLU_0007 = rtrim(ltrim(t1.IM_COLU_0007 + t2.IM_COLU_0007 + t3.IM_COLU_0007)),
       @VSNU_COLU_0008 = rtrim(ltrim(t1.IM_COLU_0008 + t2.IM_COLU_0008 + t3.IM_COLU_0008)), 
       @VSNU_COLU_0009 = rtrim(ltrim(t1.IM_COLU_0009 + t2.IM_COLU_0009 + t3.IM_COLU_0009)),
       @VSNU_COLU_0010 = rtrim(ltrim(t1.IM_COLU_0010 + t2.IM_COLU_0010 + t3.IM_COLU_0010)), 
       @VSNU_COLU_0011 = rtrim(ltrim(t1.IM_COLU_0011 + t2.IM_COLU_0011 + t3.IM_COLU_0011)),
       @VSNU_COLU_0012 = rtrim(ltrim(t1.IM_COLU_0012 + t2.IM_COLU_0012 + t3.IM_COLU_0012)), 
       @VSNU_COLU_0013 = rtrim(ltrim(t1.IM_COLU_0013 + t2.IM_COLU_0013 + t3.IM_COLU_0013)),
       @VSNU_COLU_0014 = rtrim(ltrim(t1.IM_COLU_0014 + t2.IM_COLU_0014 + t3.IM_COLU_0014)), 
       @VSNU_COLU_0015 = rtrim(ltrim(t1.IM_COLU_0015 + t2.IM_COLU_0015 + t3.IM_COLU_0015))
 from TDREPO_FINA t1, TDREPO_FINA t2, TDREPO_FINA t3
 where t1.CO_EMPR = @ISCO_EMPR
   and t1.CO_REPO = @ISCO_REPO
   and t1.NU_SECU = 1
   and t2.NU_SECU = 2
   and t3.NU_SECU = 3
   and t2.CO_EMPR = t1.CO_EMPR
   and t2.CO_REPO = t1.CO_REPO
   and t3.CO_EMPR = t1.CO_EMPR
   and t3.CO_REPO = t1.CO_REPO

If @ISCO_CNTA_EMPR = ''
  Select @VSCO_CNTA_TEMP = NULL
Else
  Select @VSCO_CNTA_TEMP = @ISCO_CNTA_EMPR
If @ISTI_AUXI_EMPR = ''
  Select @VSTI_AUXI_TEMP = NULL
Else
  Select @VSTI_AUXI_TEMP = @ISTI_AUXI_EMPR
If @ISCO_AUXI_EMPR = ''
  Select @VSCO_AUXI_TEMP = NULL
Else
  Select @VSCO_AUXI_TEMP = @ISCO_AUXI_EMPR
If @ISCO_ORDE_SERV = ''
  Select @VSCO_ORDE_TEMP = NULL
Else
  Select @VSCO_ORDE_TEMP = @ISCO_ORDE_SERV

Select @VNNU_CONT = 1
Select @VSNU_COLU = @VSNU_COLU_0001 
/*--- Pregunto por el tipo de reporte para armar el select ---*/
if @ISTI_CONS_SALD = 'SAL'
  select @VSDE_SELE_0001 = 'select Isnull(sum(Isnull(IM_CARG_MESE, 0) - Isnull(IM_ABON_MESE, 0)), 0) From TASALD_EMPR  
       where CO_EMPR = "' + @ISCO_EMPR + '" and NU_CNTB_EMPR = ' + convert(char(01), @INNU_CNTB_EMPR)
Else if @ISTI_CONS_SALD = 'FLE'
  select @VSDE_SELE_0001 = 'select Isnull(sum(Isnull(IM_TOTA_CARG, 0) - Isnull(IM_TOTA_ABON, 0)), 0) From TASALD_FLEF
      where CO_EMPR = "' + @ISCO_EMPR + '" and NU_CNTB_EMPR = ' + convert(char(01), @INNU_CNTB_EMPR)
 
/*--- Para agrupar por formulas --*/
if @ISCO_FORM = '@ACR'
  if @ISTI_CONS_SALD = 'SAL'
    Select @VSDE_SELE_0004 = 'having Isnull(sum(Isnull(IM_CARG_MESE, 0) - Isnull(IM_ABON_MESE, 0)), 0) < 0'
  Else  
    Select @VSDE_SELE_0004 = 'having Isnull(sum(Isnull(IM_TOTA_CARG, 0) - Isnull(IM_TOTA_ABON, 0)), 0) < 0'
if @ISCO_FORM = '@DEU'
  if @ISTI_CONS_SALD = 'SAL'
   Select @VSDE_SELE_0004 = 'having Isnull(sum(Isnull(IM_CARG_MESE, 0) - Isnull(IM_ABON_MESE, 0)), 0) > 0'
  Else
   Select @VSDE_SELE_0004 = 'having Isnull(sum(Isnull(IM_TOTA_CARG, 0) - Isnull(IM_TOTA_ABON, 0)), 0) > 0'
if @ISCO_FORM = '@SAL'
  Select @VSDE_SELE_0004 = ''

/*-- CASOS ---*/
/*-- Si la cuenta no es nula --*/
if @ISTI_CONS_SALD = 'FLE'
 Begin
  Select @VSCO_CNTA_TEMP = @VSCO_CNTA_TEMP + '%'
  Select @VSDE_SELE_0003 = ' and CO_CNTA_FLEF Like "' + @VSCO_CNTA_TEMP + '" '
 End
Else
 Begin
   if @VSCO_CNTA_TEMP IS NOT NULL
    Begin
     if @VSTI_AUXI_TEMP IS NOT NULL
      Begin
       if @VSCO_AUXI_TEMP IS NULL
         Begin
          Select @VSCO_CNTA_TEMP = @VSCO_CNTA_TEMP + '%'
          if @VSCO_ORDE_TEMP IS NULL
            Select @VSDE_SELE_0003 = ' and CO_CNTA_EMPR Like "' + @VSCO_CNTA_TEMP + '" 
                                       and TI_AUXI_EMPR = "' + @VSTI_AUXI_TEMP + '" '
          if @VSCO_ORDE_TEMP IS NOT NULL     
            Select @VSDE_SELE_0003 = ' and CO_CNTA_EMPR Like "' + @VSCO_CNTA_TEMP + '"
                                       and TI_AUXI_EMPR = "' + @VSTI_AUXI_TEMP + '"
                                       and CO_ORDE_SERV = "' + @VSCO_ORDE_TEMP + '" '
        End
      if @VSCO_AUXI_TEMP IS NOT NULL
        Begin
            Select @VSCO_CNTA_TEMP = @VSCO_CNTA_TEMP + '%'
          if @VSCO_ORDE_TEMP IS NULL
            Select @VSDE_SELE_0003 = ' and CO_CNTA_EMPR Like "' + @VSCO_CNTA_TEMP + '"
                                       and TI_AUXI_EMPR = "' + @VSTI_AUXI_TEMP + '"
                                       and CO_AUXI_EMPR = "' + @VSCO_AUXI_TEMP + '" '
          if @VSCO_ORDE_TEMP IS NOT NULL     
            Select @VSDE_SELE_0003 = ' and CO_CNTA_EMPR Like "' + @VSCO_CNTA_TEMP + '"
                                       and TI_AUXI_EMPR = "' + @VSTI_AUXI_TEMP + '"
                                       and CO_AUXI_EMPR = "' + @VSCO_AUXI_TEMP + '"
                                       and CO_ORDE_SERV = "' + @VSCO_ORDE_TEMP + '" '
        End
    End
   if @VSTI_AUXI_TEMP IS NULL
    Begin
     if @VSCO_ORDE_TEMP IS NOT NULL
      Begin
        Select @VSCO_CNTA_TEMP = @VSCO_CNTA_TEMP + '%'
        Select @VSDE_SELE_0003 = ' and CO_CNTA_EMPR Like "' + @VSCO_CNTA_TEMP + '"
                                   and CO_ORDE_SERV = "' + @VSCO_ORDE_TEMP + '" '
      End
     if @VSCO_ORDE_TEMP IS NULL
        Select @VSDE_SELE_0003 = ' and CO_CNTA_EMPR = "' + @VSCO_CNTA_TEMP + '"
                                   and TI_AUXI_EMPR IS NULL  
                                   and CO_AUXI_EMPR IS NULL
                                   and CO_ORDE_SERV IS NULL ' 
    End
  End

  /*--- Si la cuenta es nula ---*/
   if @VSCO_CNTA_TEMP IS NULL
    Begin
      if @VSTI_AUXI_TEMP IS NOT NULL
       Begin
        if @VSCO_AUXI_TEMP IS NULL
         Begin
          if @VSCO_ORDE_TEMP IS NULL
             Select @VSDE_SELE_0003 = ' and TI_AUXI_EMPR = "' + @VSTI_AUXI_TEMP + '" '
          if @VSCO_ORDE_TEMP IS NOT NULL     
             Select @VSDE_SELE_0003 = ' and TI_AUXI_EMPR = "' + @VSTI_AUXI_TEMP + '"
                                        and CO_ORDE_SERV = "' + @VSCO_ORDE_TEMP + '" '
         End
        if @VSCO_AUXI_TEMP IS NOT NULL
         Begin
          if @VSCO_ORDE_TEMP IS NULL
             Select @VSDE_SELE_0003 = ' and TI_AUXI_EMPR = "' + @VSTI_AUXI_TEMP + '" 
                                        and CO_AUXI_EMPR = "' + @VSCO_AUXI_TEMP + '" '
          if @VSCO_ORDE_TEMP IS NOT NULL     
             Select @VSDE_SELE_0003 = ' and TI_AUXI_EMPR = "' + @VSTI_AUXI_TEMP + '"
                                        and CO_AUXI_EMPR = "' + @VSCO_AUXI_TEMP + '"
                                        and CO_ORDE_SERV = "' + @VSCO_ORDE_TEMP + '" '
         End
      End
     if @VSTI_AUXI_TEMP IS NULL
      if @VSCO_ORDE_TEMP IS NOT NULL
        Select @VSDE_SELE_0003 = ' and CO_ORDE_SERV = "' + @VSCO_ORDE_TEMP + '" '
    End
 End


Select @VNNU_CONT = 1
Select @VSNU_COLU = @VSNU_COLU_0001

While @VNNU_CONT <= 15
 Begin  
  
   select @VNIM_SALD = 0
   select @CNIM_SALD_TEMP = 0

  /*---- Para saber si es acumulado o del periodo ---*/
  if substring(@VSNU_COLU,7,1) = 'P'
   Select @VSDE_SELE_0002 = 'and NU_ANNO = '+ substring(@VSNU_COLU, 1, 4) + '
                             and NU_MESE = '+ substring(@VSNU_COLU, 5, 2)
  if substring(@VSNU_COLU,7,1) = 'A' 
   Select @VSDE_SELE_0002 = 'and NU_ANNO = '+ substring(@VSNU_COLU, 1, 4) + '
                             and NU_MESE <= '+ substring(@VSNU_COLU, 5, 2)

   Execute ('Declare CU_TASALD_EMPR_Q23_1 cursor for ' + @VSDE_SELE_0001 + @VSDE_SELE_0002
                 + @VSDE_SELE_0003 + @VSDE_SELE_0004)

   open CU_TASALD_EMPR_Q23_1
   Fetch CU_TASALD_EMPR_Q23_1 into @CNIM_SALD_TEMP
  
   While @@Fetch_status = 0 
    Begin
      Select @VNIM_SALD = @CNIM_SALD_TEMP
      
      Fetch CU_TASALD_EMPR_Q23_1 into @CNIM_SALD_TEMP
    End
 
    close CU_TASALD_EMPR_Q23_1
    deallocate CU_TASALD_EMPR_Q23_1

  Select @VNNU_CONT = @VNNU_CONT + 1

  if @VNIM_SALD IS NULL
   Select @VNIM_SALD = 0

  if @VSNU_COLU IS NULL
    Select @VNIM_SALD = 0
  

  if @VNNU_CONT = 2
   Begin
    insert into #TWSALD_EMPR_Q23_1(IM_MVTO_0001)
    values(convert(varchar(20),@VNIM_SALD))
    Select @VSNU_COLU = @VSNU_COLU_0002
   End
  Else If @VNNU_CONT = 3
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0002 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0003
   End
  Else If @VNNU_CONT = 4
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0003 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0004
   End
  Else If @VNNU_CONT = 5
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0004 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0005
   End
  Else If @VNNU_CONT = 6
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0005 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0006
   End
  Else If @VNNU_CONT = 7
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0006 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0007
   End
  Else If @VNNU_CONT = 8
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0007 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0008
   End
  Else If @VNNU_CONT = 9
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0008 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0009
   End
  Else If @VNNU_CONT = 10
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0009 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0010
   End
  Else If @VNNU_CONT = 11
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0010 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0011
   End
  Else If @VNNU_CONT = 12
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0011 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0012
   End
  Else If @VNNU_CONT = 13
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0012 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0013
   End
  Else If @VNNU_CONT = 14
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0013 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0014
   End
  Else If @VNNU_CONT = 15
   Begin
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0014 = convert(varchar(20),@VNIM_SALD)
    Select @VSNU_COLU = @VSNU_COLU_0015
   End
  Else If @VNNU_CONT = 16
    Update #TWSALD_EMPR_Q23_1
    Set IM_MVTO_0015 = convert(varchar(20),@VNIM_SALD)

  Select @VNIM_SALD = 0
  Select @CNIM_SALD_TEMP = 0
 End

select * from #TWSALD_EMPR_Q23_1

return
/*------------------------------------- Fin --------------------------------------*/


















GO
